#!/usr/bin/env python
# -*- coding: utf-8 -*-

'''
Model模块，定义了数据库连接和操作
'''

from lib.sqldata import ModelBase


class ModelStatus(ModelBase):
    '数据库模型，在基类Base上定义了status，用于实际项目中'
    STATUS = {
            'exist': 1,
            'removed': 0}

    def select_by_id(self, info_id):
        '根据主键ID获取记录'
        where_dict = {'id': info_id}
        record = self.select(where_dict)
        return record

    def select_all(self, where_dict=None, columns=None, more=None, table=None):
        '根据传入参数获取记录，此方法获取所有记录'
        if not where_dict:
            where_dict = {}
        where_dict['status'] = self.STATUS['exist']
        records = super(ModelStatus, self).select_all(
                where_dict, columns, more, table)

        return records

    def insert_from_dict(self, info_dict, table=None):
        '根据传入的字典插入数据'
        info_dict['status'] = self.STATUS['exist']

        count = super(ModelStatus, self).insert_from_dict(info_dict, table)
        return count


class ModelUser(ModelStatus):
    '用户信息'
    TABLE_NAME = 'user_info'

    def get_user_by_passwd(self, username, passwd):
        '根据用户名和密码查找用户信息'
        check_info = {}
        check_info['username'] = username
        check_info['passwd'] = passwd
        record = self.select(where_dict=check_info)
        return record

    @ModelBase.transaction
    def create(self, user_info):
        '根据用户信息创建用户'
        check_info = {'username': user_info['username']}
        more = 'for update'
        record = self.select(where_dict=check_info, more=more)
        if record:
            self._conn.rollback()
            return None

        user_info['account'] = 0
        count = self.insert_from_dict(user_info)
        if not count:
            self._conn.rollback()
            return None

        record = self.select(where_dict=check_info, more=more)
        return record

    def is_username_exist(self, username):
        '检查用户名是否存在'
        check_info = {'username': username}
        record = self.select(where_dict=check_info)
        return record


class ModelItem(ModelStatus):
    '商品信息'
    TABLE_NAME = 'item_info'

    def _select_by_condition(self, where_str, where_value, more=None):
        '根据条件选择商品信息和标签信息'
        table_order = 'order_info'

        columns = [
                '`%s`.id id' % self.TABLE_NAME,
                'itemname',
                'price',
                'detail',
                'owner_id',
                'add_time',
                '`%s`.id order_id' % table_order,
                'buyer_id',
                'buy_time',
                ]

        # 联合订单表查询商品信息
        sql_str = 'select %s from `%s` left join `%s` on %s where %s'
        column_str = ', '.join(columns)
        on_str = '`%s`.id=`%s`.item_id' % (
                self.TABLE_NAME, table_order)
        where_str += ' and `%s`.status=%s' % (
                self.TABLE_NAME, self.STATUS['exist'])
        sql_str = sql_str % (
                column_str, self.TABLE_NAME, table_order, on_str, where_str)
        if more:
            sql_str += ' ' + more

        self._cursor.execute(sql_str, where_value)
        records = self._cursor.fetchall()
        if not records:
            return None

        # 查询标签信息
        table_tag = ModelTag.TABLE_NAME
        table_belong = 'tag_belong'
        sql_str = 'select `%s`.name' % table_tag
        sql_str += ' from `%s`, `%s`' % (table_tag, table_belong)
        sql_str += ' where `%s`.tag_id=`%s`.id' % (table_belong, table_tag)
        sql_str += ' and `%s`.status=%s' % (table_tag, self.STATUS['exist'])
        sql_str += ' and `%s`.status=%s' % (table_belong, self.STATUS['exist'])
        sql_str += ' and `%s`.item_id=%s' % (table_belong, '%s')
        for item_info in records:
            self._cursor.execute(sql_str, item_info['id'])
            tag_infos = self._cursor.fetchall()
            if not tag_infos:
                item_info['tags'] = []
            else:
                item_info['tags'] = [rec['name'] for rec in tag_infos]

        return records

    def _count_by_condition(self, where_str, where_value):
        '根据条件统计商品数'
        table_order = 'order_info'

        # 联合订单表查询商品信息
        sql_str = 'select count(*) from `%s` left join `%s` on %s where %s'
        on_str = '`%s`.id=`%s`.item_id' % (
                self.TABLE_NAME, table_order)
        where_str += ' and `%s`.status=%s' % (
                self.TABLE_NAME, self.STATUS['exist'])
        sql_str = sql_str % (
                self.TABLE_NAME, table_order, on_str, where_str)

        self._cursor.execute(sql_str, where_value)
        record = self._cursor.fetchone()
        count = record['count(*)']
        return count

    @ModelBase.transaction
    def create(self, item_info, photo_info, tags):
        '新建商品'
        import datetime

        # 插入商品记录
        add_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        item_info['add_time'] = add_time
        count = self.insert_from_dict(item_info)
        if count <= 0:
            self._conn.rollback()
            return None

        # 获取刚插入的记录id
        sql_str = 'select id from `%s`' % self.TABLE_NAME
        sql_str += ' where id=last_insert_id() and status=%s'
        self._cursor.execute(sql_str, self.STATUS['exist'])
        item_info = self._cursor.fetchone()
        if not item_info:
            self._conn.rollback()
            return None

        # 插入图片记录
        photo_info['item_id'] = item_info['id']
        photo_info['priority'] = ModelPhoto.PRIORITY['default']
        count = self.insert_from_dict(photo_info, 'photo')
        if count <= 0:
            self._conn.rollback()
            return None

        # 插入标签记录
        for tag in tags:
            # 如果标签库尚未加此标签，则先添加
            tag_dict = {'name': tag}
            tag_info = self.select(
                    where_dict=tag_dict,
                    more='lock in share mode',
                    table=ModelTag.TABLE_NAME)
            if not tag_info:
                self.insert_from_dict(tag_dict, table=ModelTag.TABLE_NAME)
                tag_info = self.select(
                        where_dict=tag_dict,
                        table=ModelTag.TABLE_NAME)
            if not tag_info:
                self._conn.rollback()
                return None

            # 添加标签关系记录
            belong_info = {}
            belong_info['tag_id'] = tag_info['id']
            belong_info['item_id'] = item_info['id']
            count = self.insert_from_dict(belong_info, table='tag_belong')
            if not count:
                self._conn.rollback()
                return None

        return item_info

    def select_by_owner(self, owner_id, limits=None):
        '根据拥有者查询商品信息'
        where_str = 'owner_id=%s'
        more = 'order by add_time desc'
        if limits:
            more += ' limit %d,%d' % limits
        records = self._select_by_condition(where_str, owner_id, more)
        return records

    def count_by_owner(self, owner_id):
        '统计用户拥有的商品数'
        where_str = 'owner_id=%s'
        count = self._count_by_condition(where_str, owner_id)
        return count

    def select_by_buyer(self, buyer_id, limits=None):
        '根据购买者查询商品信息'
        where_str = 'buyer_id=%s'
        more = 'order by add_time desc'
        if limits:
            more += ' limit %d,%d' % limits
        records = self._select_by_condition(where_str, buyer_id, more)
        return records

    def count_by_buyer(self, buyer_id):
        '统计用户买到的商品数'
        where_str = 'buyer_id=%s'
        count = self._count_by_condition(where_str, buyer_id)
        return count

    def select_by_available(self, limits=None):
        '查询未卖的商品信息，按时间排序，只取前K个'
        where_str = 'buyer_id is NULL'
        more = 'order by add_time desc'
        if limits:
            more += ' limit %d,%d' % limits
        records = self._select_by_condition(where_str, (), more)
        return records

    def count_by_available(self):
        '统计未卖的商品数'
        where_str = 'buyer_id is NULL'
        count = self._count_by_condition(where_str, ())
        return count

    def _get_price_str(self, prices, with_and=True):
        '根据prices制定查询语句'
        if prices:
            where_str = ' and' if with_and else ''
            prices = tuple(prices)
            if len(prices) == 1:
                where_str += ' price>=%d' % prices
            else:
                where_str += ' price>=%d and price<=%d' % prices
        else:
            where_str = ''

        return where_str

    def search_by_name(self, query, limits=None, prices=None):
        '搜索商品名查找商品信息，可选根据价格字段'
        where_str = 'itemname like %s'
        where_value = ['%' + query + '%']
        where_str += self._get_price_str(prices)
        more = 'order by add_time desc'
        if limits:
            more += ' limit %d,%d' % limits
        records = self._select_by_condition(where_str, where_value, more)
        return records

    def count_by_search_name(self, query, prices=None):
        '搜索商品名查找商品数'
        where_str = 'itemname like %s'
        where_value = ['%' + query + '%']
        where_str += self._get_price_str(prices)
        count = self._count_by_condition(where_str, where_value)
        return count

    def search_by_price(self, prices, limits=None):
        '根据价格字段查找商品信息'
        where_str = self._get_price_str(prices, False)
        more = 'order by add_time desc'
        if limits:
            more += ' limit %d,%d' % limits
        records = self._select_by_condition(where_str, (), more)
        return records

    def count_by_search_price(self, prices):
        '根据价格字段查找商品数'
        where_str = self._get_price_str(prices, False)
        count = self._count_by_condition(where_str, ())
        return count

    def search_by_tags(
            self, query, limits=None, prices=None,
            is_count=False, is_fuzzy=True):
        '搜索标签查找商品信息，可选是否需要模糊查询'
        sql_str = 'select id from `%s`' % ModelTag.TABLE_NAME
        sql_str += ' where name like %s'

        if is_fuzzy:
            query = '%' + query + '%'
        self._cursor.execute(sql_str, query)
        records = self._cursor.fetchall()
        if not records:
            return 0 if is_count else None
        tag_ids = [rec['id'] for rec in records]

        if is_count:
            count = self.count_by_tags(tag_ids, prices)
            return count
        else:
            item_infos = self.select_by_tags(tag_ids, limits, prices)
            return item_infos

    def select_by_tags(self, tag_ids, limits=None, prices=None):
        '根据标签id列表找商品信息，按时间排序，只取前K个'
        table_belong = 'tag_belong'

        # 由标签ID找到对应商品ID（去重，时间排序，前K个）
        join_str = '`%s`.id=`%s`.item_id' % (self.TABLE_NAME, table_belong)
        join_str += ' and `%s`.status=%d' % (
                self.TABLE_NAME, self.STATUS['exist'])
        join_str += ' and `%s`.status=%d' % (
                table_belong, self.STATUS['exist'])
        join_str += self._get_price_str(prices)
        tag_str = ' or '.join(['tag_id=%s'] * len(tag_ids))

        select_str = 'select distinct `%s`.id from `%s`, `%s`'
        select_str %= (self.TABLE_NAME, self.TABLE_NAME, table_belong)
        cond_str = ' where %s and (%s) order by add_time desc'
        cond_str %= (join_str, tag_str)
        if limits:
            cond_str += ' limit %d,%d' % limits
        sql_str = select_str + cond_str

        self._cursor.execute(sql_str, tag_ids)
        records = self._cursor.fetchall()
        if not records:
            return None
        item_ids = [rec['id'] for rec in records]

        # 根据商品ID获得商品信息
        item_infos = []
        for item_id in item_ids:
            item_info = self.select_by_id_join(item_id)
            if item_info:
                item_infos.append(item_info)

        return item_infos

    def count_by_tags(self, tag_ids, prices=None):
        '根据标签id列表统计商品个数'
        table_belong = 'tag_belong'

        # 由标签ID找到对应商品ID（去重，时间排序，前K个）
        join_str = '`%s`.id=`%s`.item_id' % (self.TABLE_NAME, table_belong)
        join_str += ' and `%s`.status=%d' % (
                self.TABLE_NAME, self.STATUS['exist'])
        join_str += ' and `%s`.status=%d' % (
                table_belong, self.STATUS['exist'])
        join_str += self._get_price_str(prices)
        tag_str = ' or '.join(['tag_id=%s'] * len(tag_ids))

        select_str = 'select count(distinct item_id) from `%s`, `%s`'
        select_str %= (self.TABLE_NAME, table_belong)
        cond_str = ' where %s and (%s)'
        cond_str %= (join_str, tag_str)
        sql_str = select_str + cond_str

        self._cursor.execute(sql_str, tag_ids)
        record = self._cursor.fetchone()
        if not record:
            return 0
        else:
            return record['count(distinct item_id)']

    def select_by_id_join(self, item_id):
        '根据ID选择商品信息和标签信息'
        where_str = '`%s`' % self.TABLE_NAME
        where_str += '.id=%s'
        records = self._select_by_condition(where_str, (item_id, ))
        if not records:
            return None
        return records[0]

    @ModelBase.transaction
    def buy(self, item_id, buyer_id):
        '购买商品'
        import datetime

        table_user = ModelUser.TABLE_NAME
        table_order = 'order_info'

        # 锁定商品
        item_info = self.select(
                where_dict={'id': item_id},
                more="for update")
        if not item_info:
            self._conn.rollback()
            return False

        # 锁定买家
        buyer_info = self.select(
                where_dict={'id': buyer_id},
                table=table_user,
                more="for update")
        if not buyer_info:
            self._conn.rollback()
            return False

        # 锁定卖家
        owner_info = self.select(
                where_dict={'id': item_info['owner_id']},
                table=table_user,
                more="for update")
        if not owner_info:
            self._conn.rollback()
            return False

        # 检查商品是否买家的，是否够钱买
        owner_id = owner_info['id']
        if owner_id == buyer_id:
            self._conn.rollback()
            return False
        if item_info['price'] > buyer_info['account']:
            self._conn.rollback()
            return False

        # 检查商品是否未被买
        order_info = self.select(
                where_dict={'item_id': item_id},
                table=table_order,
                more="for update")
        if order_info:
            self._conn.rollback()
            return False

        # 添加商品订单记录
        buy_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        order_info = {}
        order_info['item_id'] = item_id
        order_info['buyer_id'] = buyer_id
        order_info['buy_time'] = buy_time
        count = self.insert_from_dict(order_info, table=table_order)
        if not count:
            self._conn.rollback()
            return False

        # 更新买家记录
        sql_str = 'update `%s`' % table_user
        sql_str += ' set account=account-%s where id=%s'
        count = self._cursor.execute(sql_str, (item_info['price'], buyer_id))
        if not count:
            self._conn.rollback()
            return False

        # 更新卖家记录
        sql_str = 'update `%s`' % table_user
        sql_str += ' set account=account+%s where id=%s'
        count = self._cursor.execute(sql_str, (item_info['price'], owner_id))
        if not count:
            self._conn.rollback()
            return False

        return True

    @ModelBase.transaction
    def remove(self, item_id, owner_id):
        '删除商品'

        table_order = 'order_info'

        # 锁定商品
        item_info = self.select(
                where_dict={'id': item_id},
                more="for update")
        if not item_info:
            self._conn.rollback()
            return False

        # 判断是否本人
        if item_info['owner_id'] != owner_id:
            self._conn.rollback()
            return False

        # 判断是否已卖
        where_dict = {'item_id': item_id}
        record = self.select(
                where_dict=where_dict,
                table=table_order,
                more='lock in share mode')
        if record:
            self._conn.rollback()
            return False

        # 移除商品
        where_dict = {'id': item_id}
        info_dict = {'status': self.STATUS['removed']}
        count = self.update_from_dict(
            where_dict=where_dict,
            info_dict=info_dict)
        if not count:
            self._conn.rollback()
            return False

        return True


class ModelPhoto(ModelStatus):
    '图片库模型'
    TABLE_NAME = 'photo'
    PRIORITY = {'default': '1'}

    def select_by_item_id(self, item_id):
        '根据商品ID查找默认图片'
        where_dict = {}
        where_dict['item_id'] = item_id
        where_dict['priority'] = self.PRIORITY['default']
        record = self.select(where_dict=where_dict)
        return record


class ModelTag(ModelStatus):
    '标签库模型'
    TABLE_NAME = 'tag'


class ModelCharge(ModelStatus):
    '充值订单模型'
    TABLE_NAME = 'charge_info'
    PAY_STATUS = {
            'canceled': 0,
            'unpaid': 1,
            'paid': 2}

    @ModelBase.transaction
    def create(self, user_id, amount):
        '创建充值订单'
        import datetime

        add_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')

        charge_info = {}
        charge_info['user_id'] = user_id
        charge_info['amount'] = amount
        charge_info['add_time'] = add_time

        count = self.insert_from_dict(charge_info)
        if not count:
            self._conn.rollback()
            return None

        charge_info['id'] = self._conn.insert_id()

        return charge_info

    @ModelBase.transaction
    def remove(self, charge_id):
        '移除充值订单'

        info_dict = {'status': self.STATUS['removed']}
        where_dict = {'id': charge_id}

        count = self.update_from_dict(info_dict, where_dict)
        if not count:
            self._conn.rollback()
            return False

        return True

    @ModelBase.transaction
    def add_pay_sn(self, charge_id, pay_sn):
        '补充充值订单的支付号与支付状态'
        info_dict = {'pay_sn': pay_sn, 'pay_status': self.PAY_STATUS['unpaid']}
        where_dict = {'id': charge_id}

        count = self.update_from_dict(info_dict, where_dict)
        if not count:
            self._conn.rollback()
            return False

        return True

    def select_by_sn(self, pay_sn):
        '根据支付SN号查找订单信息'
        where_dict = {
                'pay_sn': pay_sn,
                'pay_status': self.PAY_STATUS['unpaid']}
        charge_info = self.select(where_dict)
        return charge_info

    def select_by_unpaid(self, user_id=None):
        '查找所有未支付状态的订单，可以指定某用户'
        where_dict = {
                'pay_status': self.PAY_STATUS['unpaid']}
        if user_id:
            where_dict['user_id'] = user_id
        charge_infos = self.select_all(where_dict)
        return charge_infos

    @ModelBase.transaction
    def update_canceled(self, pay_sn):
        '根据订单支付号更新充值订单状态为取消'
        info_dict = {'pay_status': self.PAY_STATUS['canceled']}
        where_dict = {'pay_sn': pay_sn}

        count = self.update_from_dict(info_dict, where_dict)
        if not count:
            self._conn.rollback()
            return False

        return True

    @ModelBase.transaction
    def update_paid(self, pay_sn):
        '根据订单支付号更新充值订单状态为已付款'
        table_user = ModelUser.TABLE_NAME

        info_dict = {'pay_status': self.PAY_STATUS['paid']}
        where_dict = {'pay_sn': pay_sn}

        count = self.update_from_dict(info_dict, where_dict)
        if not count:
            self._conn.rollback()
            return False

        # 为用户补充余额
        charge_info = self.select(where_dict)
        if not charge_info:
            self._conn.rollback()
            return False

        amount = charge_info['amount']
        user_id = charge_info['user_id']
        sql_str = 'update `%s`' % table_user
        sql_str += ' set account=account+%s where id=%s'
        count = self._cursor.execute(sql_str, (amount, user_id))
        if not count:
            self._conn.rollback()
            return False

        return True
